package com.osfw.dbadminer.service.database.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.osfw.dbadminer.domain.database.SqlExecuteParam;
import com.osfw.dbadminer.domain.database.SqlExecuteResult;
import com.osfw.dbadminer.entity.connect.DbaConnect;
import com.osfw.dbadminer.mapper.connect.DbaConnectMapper;
import com.osfw.dbadminer.service.database.IDbaDataSqlService;
import com.osfw.dbadminer.service.jdbc.JdbcBaseFactory;
import com.osfw.dbadminer.service.jdbc.JdbcBaseService;
import com.osfw.dbadminer.util.SQLParserUtils;
import com.osfw.framework.config.excel.ExcelColumnWidthStrategy;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

/**
 * 数据库操作 服务层处理
 */
@Service
public class DbaDataSqlServiceImpl implements IDbaDataSqlService {

    public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @Resource
    private DbaConnectMapper connectMapper;

    @Override
    public List<SqlExecuteResult> execute(SqlExecuteParam param) {
        List<SqlExecuteResult> results = new ArrayList<>();
        DbaConnect connect = connectMapper.selectById(param.getConnectId());
        JdbcBaseService jdbcService = JdbcBaseFactory.createAndInit(connect);
        for (String sql : SQLParserUtils.getAllSQLStatements(param.getSqlCode())) {
            SqlExecuteResult result = jdbcService.executeStmt(param.getDatabaseName(), sql, param.getPage(), param.getSize());
            results.add(result);
            // SqlExecuteInfo executeInfo = new SqlExecuteInfo(result.getStmt(), result.getMsg(), result.getTimes());
            // DwrMessagePush.sendMessageToUsers(sqlExecuteParam.getUserId(), JSONUtil.toJsonStr(executeInfo));
            if (result.isError() || result.isWarn()) {
                return results;
            }
        }
        return results;
    }


    @Override
    public void exportExcel(SqlExecuteParam exportParam, HttpServletResponse response) throws IOException, InterruptedException {

        setExportHeader(response);

        int pageSize = 50000;
        int pages = exportParam.getTotal() < pageSize ? 1 : (int) Math.ceil((double) exportParam.getTotal() / (double) pageSize);
        ;

        ExecutorService executorService = Executors.newFixedThreadPool(pages);
        CountDownLatch countDownLatch = new CountDownLatch(pages);

        DbaConnect connect = connectMapper.selectById(exportParam.getConnectId());
        JdbcBaseService jdbcService = JdbcBaseFactory.createAndInit(connect);

        Map<Integer, List<Map<String, Object>>> pageMap = new HashMap<>();
        for (int i = 0; i < pages; i++) {
            int finalI = i;
            executorService.submit(new Runnable() {
                @Override
                public void run() {
                    SqlExecuteResult result = jdbcService.executeStmt(exportParam.getDatabaseName(), exportParam.getSqlCode(), finalI + 1, pageSize);
                    pageMap.put(finalI, result.getData());
                    countDownLatch.countDown();
                }
            });
        }
        countDownLatch.await();
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
            for (Map.Entry<Integer, List<Map<String, Object>>> entry : pageMap.entrySet()) {
                Integer num = entry.getKey();
                List<Map<String, Object>> list = entry.getValue();
                List<List<String>> head = new ArrayList<>();
                List<List<Object>> data = new ArrayList<>();
                if (!list.isEmpty()) {
                    List<String> keys = list.get(0).entrySet().stream().map(x -> x.getKey()).collect(Collectors.toList());
                    head = head(keys);
                    data = dataList(list, keys);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(num, "第 " + +(num + 1) + " 页数据").build();
                writeSheet.setHead(head);
                writeSheet.setCustomWriteHandlerList(Arrays.asList(new ExcelColumnWidthStrategy()));
                excelWriter.write(data, writeSheet);
            }
        }

    }

    private static void setExportHeader(HttpServletResponse response) {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "导出数据.xlsx");
    }

    //设置表头
    private List<List<String>> head(List<String> headMap) {
        List<List<String>> list = new ArrayList<List<String>>();

        for (String head : headMap) {
            List<String> headList = new ArrayList<String>();
            headList.add(head);
            list.add(headList);
        }
        return list;
    }

    //设置导出的数据内容
    private List<List<Object>> dataList(List<Map<String, Object>> dataList, List<String> dataStrMap) {
        List<List<Object>> list = new ArrayList<List<Object>>();
        for (Map<String, Object> map : dataList) {
            List<Object> data = new ArrayList<Object>();
            for (int i = 0; i < dataStrMap.size(); i++) {
                data.add(map.get(dataStrMap.get(i)));
            }
            list.add(data);
        }
        return list;
    }

}
